﻿/*用户登录*/
USE LibraryDataBase_wl
GO
/****** Object:  StoredProcedure [dbo].[GetLogin]    Script Date: 09/09/2010 22:47:59 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
--------------------------------------用户表存储过程------------------------------------------
/*用户登录*/
create proc [dbo].[GetLogin](@LoginName varchar(50),@LoginPass varchar(50))
as
	select UserName,UserPass from TabUser
		where UserName = @LoginName and UserPass = @LoginPass
GO

USE LibraryDataBase_wl
GO
IF EXISTS (SELECT name FROM sysobjects 
WHERE name='GetOpenBookInfoAll' AND type='p')
DROP PROC GetOpenBookInfoAll
GO
CREATE PROCEDURE GetOpenBookInfoAll
(
  @message varchar(50)
)
AS
SET NOCOUNT ON
IF(@message = '')
begin
  select a.BorrowBookID ,b.BookID,b.BookName,b.BookNumber,c.TypeName,d.StateName,b.BookAuther,b.BookPublicTime,b.BookPrice,b.IsDisk,b.Remark
  from TabBorrowBooks as a ,TabBook as b,TabBookType as c,TabBookState as d
  where  a.BookID = b.BookID and b.TypeID = c.TypeID and b.StateID = d.StateID
end
else
begin
 select a.BorrowBookID ,b.BookID,b.BookName,b.BookNumber,c.TypeName,d.StateName,b.BookAuther,b.BookPublicTime,b.BookPrice,b.IsDisk,b.Remark
  from TabBorrowBooks as a ,TabBook as b,TabBookType as c,TabBookState as d
  where  a.BookID = b.BookID and b.TypeID = c.TypeID and b.StateID = d.StateID and
  (
    b.BookID like @message + '%' or b.BookName like @message + '%' or  b.BookNumber like @message + '%' or c.TypeName like @message + '%'
    or d.StateName like @message + '%' or b.BookAuther like @message + '%' or b.BookPublicTime like @message + '%' or b.BookPrice like  @message + '%'
    or b.IsDisk like @message + '%' or b.Remark like @message + '%'
  )
end
SET NOCOUNT OFF
GO
--Open Books.
IF EXISTS (SELECT name FROM  sysobjects 
WHERE name='OpenBook' AND type='p')
DROP PROC OpenBook
GO
CREATE PROCEDURE OpenBook
(
   @BookID INT,
   @UserID INT,
   @StartTime DateTime,
   @EndTime DateTime,
   @Remark NVARCHAR
)
AS
INSERT INTO TabBorrowBooks VALUES(@BookID,@UserID,@StartTime,@EndTime,@Remark)
GO

--Colse book
IF EXISTS (SELECT name FROM sysobjects 
WHERE name='CloseBook' AND type='p')
DROP PROC CloseBook
GO
CREATE PROCEDURE CloseBook
(
  @BookNumber NVARCHAR
)
AS
SET NOCOUNT ON
DELETE TabBorrowBooks WHERE BookID = (SELECT BookID FROM TabBook WHERE BookNumber = @BookNumber)
SET NOCOUNT OFF
GO